
using Youz.Common.DBUtility;
using Youz.Common.Entity;
using Youz.CreditRepay.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Youz.CreditRepay.DAL
{
    /// <summary>
    /// Member_Recharge Data Class
    /// </summary>
    public partial class Member_RechargeDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Member_Recharge";

        public Member_RechargeDAL()
        { }

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Member_Recharge model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into [Member_Recharge](");
            strSql.Append("[RechargeID],[MemberAccount],[FromBankName],[FromBankAccountNo],[FromBankAccountName],[ToBankName],[ToBankAccountNo],[ToBankAccountName],[MoneyType],[FromAmount],[ToAmount],[Fee],[TransferTime],[Accessory],[Status],[Memo],[CreateTime],[ModifyTime],[QRCodeID]");
            strSql.Append(") values (");
            strSql.Append("@RechargeID,@MemberAccount,@FromBankName,@FromBankAccountNo,@FromBankAccountName,@ToBankName,@ToBankAccountNo,@ToBankAccountName,@MoneyType,@FromAmount,@ToAmount,@Fee,@TransferTime,@Accessory,@Status,@Memo,@CreateTime,@ModifyTime,@QRCodeID");
            strSql.Append(")");

            SqlParameter[] parameters = {

                    new SqlParameter("@RechargeID", model.RechargeID),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@FromBankName", model.FromBankName),
                    new SqlParameter("@FromBankAccountNo", model.FromBankAccountNo),
                    new SqlParameter("@FromBankAccountName", model.FromBankAccountName),
                    new SqlParameter("@ToBankName", model.ToBankName),
                    new SqlParameter("@ToBankAccountNo", model.ToBankAccountNo),
                    new SqlParameter("@ToBankAccountName", model.ToBankAccountName),
                    new SqlParameter("@MoneyType", model.MoneyType),
                    new SqlParameter("@FromAmount", model.FromAmount),
                    new SqlParameter("@ToAmount", model.ToAmount),
                    new SqlParameter("@Fee", model.Fee),
                    new SqlParameter("@TransferTime", model.TransferTime),
                    new SqlParameter("@Accessory", model.Accessory),
                    new SqlParameter("@Status", (int)model.Status),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@ModifyTime", model.ModifyTime),
                    new SqlParameter("@QRCodeID", model.QRCodeID)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Member_Recharge model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Member_Recharge] set ");
            strSql.Append("[MemberAccount]=@MemberAccount,[FromBankName]=@FromBankName,[FromBankAccountNo]=@FromBankAccountNo,[FromBankAccountName]=@FromBankAccountName,[ToBankName]=@ToBankName,[ToBankAccountNo]=@ToBankAccountNo,[ToBankAccountName]=@ToBankAccountName,[MoneyType]=@MoneyType,[FromAmount]=@FromAmount,[ToAmount]=@ToAmount,[TransferTime]=@TransferTime,[Accessory]=@Accessory,[Status]=@Status,[Memo]=@Memo,[ModifyTime]=@ModifyTime");
            strSql.Append(" where [RechargeID]=@RechargeID");

            SqlParameter[] parameters = {

                    new SqlParameter("@RechargeID", model.RechargeID),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@FromBankName", model.FromBankName),
                    new SqlParameter("@FromBankAccountNo", model.FromBankAccountNo),
                    new SqlParameter("@FromBankAccountName", model.FromBankAccountName),
                    new SqlParameter("@ToBankName", model.ToBankName),
                    new SqlParameter("@ToBankAccountNo", model.ToBankAccountNo),
                    new SqlParameter("@ToBankAccountName", model.ToBankAccountName),
                    new SqlParameter("@MoneyType", model.MoneyType),
                    new SqlParameter("@FromAmount", model.FromAmount),
                    new SqlParameter("@ToAmount", model.ToAmount),
                    new SqlParameter("@TransferTime", model.TransferTime),
                    new SqlParameter("@Accessory", model.Accessory),
                    new SqlParameter("@Status", (int)model.Status),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@ModifyTime", model.ModifyTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(String RechargeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Member_Recharge] ");
            strSql.Append(" where [RechargeID]=@RechargeID");

            SqlParameter[] parameters = {

                new SqlParameter("@RechargeID", RechargeID)

            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Recharge Get(String RechargeID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Recharge] ");
            strSql.Append(" where [RechargeID]=@RechargeID");

            SqlParameter[] parameters = {

                new SqlParameter("RechargeID", RechargeID)

            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public DataTable GetRate(String RechargeID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 r.*,c.CurrencyID,c.CurrencyName,c.Rate from [Member_Recharge] r inner join Sys_Currency c on r.MoneyType = c.CurrencyID ");
            strSql.Append(" where r.[RechargeID]=@RechargeID");

            SqlParameter[] parameters = {

                new SqlParameter("RechargeID", RechargeID)

            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Recharge Get(DataRow row)
        {
            if (row == null) return null;

            Member_Recharge model = new Member_Recharge();


            if (row["RechargeID"] != DBNull.Value)
            {
                model.RechargeID = (Int64)row["RechargeID"];
            }

            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }

            if (row["FromBankName"] != DBNull.Value)
            {
                model.FromBankName = (String)row["FromBankName"];
            }

            if (row["FromBankAccountNo"] != DBNull.Value)
            {
                model.FromBankAccountNo = (String)row["FromBankAccountNo"];
            }

            if (row["FromBankAccountName"] != DBNull.Value)
            {
                model.FromBankAccountName = (String)row["FromBankAccountName"];
            }

            if (row["ToBankName"] != DBNull.Value)
            {
                model.ToBankName = (String)row["ToBankName"];
            }

            if (row["ToBankAccountNo"] != DBNull.Value)
            {
                model.ToBankAccountNo = (String)row["ToBankAccountNo"];
            }

            if (row["ToBankAccountName"] != DBNull.Value)
            {
                model.ToBankAccountName = (String)row["ToBankAccountName"];
            }

            if (row["MoneyType"] != DBNull.Value)
            {
                model.MoneyType = (String)row["MoneyType"];
            }

            if (row["FromAmount"] != DBNull.Value)
            {
                model.FromAmount = (Decimal)row["FromAmount"];
            }

            if (row["ToAmount"] != DBNull.Value)
            {
                model.ToAmount = (Decimal)row["ToAmount"];
            }

            if (row["Fee"] != DBNull.Value)
            {
                model.Fee = (Decimal)row["Fee"];
            }

            if (row["TransferTime"] != DBNull.Value)
            {
                model.TransferTime = (DateTime)row["TransferTime"];
            }

            if (row["Accessory"] != DBNull.Value)
            {
                model.Accessory = (String)row["Accessory"];
            }

            if (row["Status"] != DBNull.Value)
            {
                model.Status = (RechargeStatus)(Int32)row["Status"];
            }

            if (row["Memo"] != DBNull.Value)
            {
                model.Memo = (String)row["Memo"];
            }

            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }

            if (row["ModifyTime"] != DBNull.Value)
            {
                model.ModifyTime = (DateTime)row["ModifyTime"];
            }

            if (row["QRCodeID"] != DBNull.Value)
            {
                model.QRCodeID = (int)row["QRCodeID"];
            }

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from [Member_Recharge] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Member_Recharge] ");

            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Member_Recharge] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@" select r.[RechargeID],r.[MemberAccount],r.[FromBankName],r.[FromBankAccountNo],r.[FromBankAccountName],r.[ToBankName],r.[ToBankAccountNo],r.[ToBankAccountName],r.[MoneyType],
                            r.[FromAmount],r.[ToAmount],r.[TransferTime],r.[Accessory],r.[Status],r.[Memo],r.[CreateTime],r.[ModifyTime],s.[Memo] as AuditOpinion,s.[CreateTime] as AuditTime,c.CurrencyID,c.CurrencyName 
                            from [Member_Recharge] r
                            left join [Sys_StatusRecord] s on cast(r.RechargeID as varchar) = s.ObjectID
                            and s.CreateTime = (select max(CreateTime) from [Sys_StatusRecord] where cast(RechargeID as varchar) = [Sys_StatusRecord].ObjectID) 
                            left join [Sys_Currency] c on r.MoneyType = c.CurrencyID ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by createTime desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        #endregion  成员方法

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="RechargeID">验证单号</param>
        /// <param name="Status">状态</param>
        /// <returns></returns>
        public bool UpdateRechargeStatus(string RechargeID, RechargeStatus Status)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Member_Recharge] set ");
            strSql.Append("[Status]=@Status ");
            strSql.Append(" where [RechargeID]=@RechargeID");

            SqlParameter[] parameters = {
                    new SqlParameter("@RechargeID", RechargeID),
                    new SqlParameter("@Status", (int)Status)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 保存审核
        /// </summary>
        /// <param name="model"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SaveAudit(Member_Recharge rechargemodel, Sys_StatusRecord statusRecordModel)
        {
            List<SqlAndParams> dict = new List<SqlAndParams>();

            #region 充值表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [Member_Recharge] set [Status]=@Status,[ModifyTime]=@ModifyTime,ToAmount=@ToAmount where [RechargeID]=@RechargeID");
            SqlParameter[] parameters = {
                    new SqlParameter("@RechargeID", rechargemodel.RechargeID),
                    new SqlParameter("@Status", (int)rechargemodel.Status),
                     new SqlParameter("@ModifyTime", rechargemodel.ModifyTime),
                     new SqlParameter("@ToAmount", rechargemodel.ToAmount) };
            dict.Add(new SqlAndParams() { Sql = strSql.ToString(), SqlParameters = parameters });
            #endregion

            #region 状态变更表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("insert into [Sys_StatusRecord](");
            strSql2.Append("[RecordId],[MemberAccount],[RecordTable],[RecordType],[BeforeValue],[AfterValue],[CreateTime],[Creator],[Memo],[ObjectID],[LoginID]");
            strSql2.Append(") values (");
            strSql2.Append("@RecordId,@MemberAccount,@RecordTable,@RecordType,@BeforeValue,@AfterValue,@CreateTime,@Creator,@Memo,@ObjectID,@LoginID");
            strSql2.Append(")");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@RecordId", statusRecordModel.RecordId),
                    new SqlParameter("@MemberAccount", statusRecordModel.MemberAccount),
                    new SqlParameter("@RecordTable", statusRecordModel.RecordTable),
                    new SqlParameter("@RecordType", statusRecordModel.RecordType),
                    new SqlParameter("@BeforeValue", statusRecordModel.BeforeValue),
                    new SqlParameter("@AfterValue", statusRecordModel.AfterValue),
                    new SqlParameter("@CreateTime", statusRecordModel.CreateTime),
                    new SqlParameter("@Creator", statusRecordModel.Creator),
                    new SqlParameter("@Memo", statusRecordModel.Memo),
                    new SqlParameter("@ObjectID", statusRecordModel.ObjectID),
                    new SqlParameter("@LoginID", statusRecordModel.LoginID) };
            dict.Add(new SqlAndParams() { Sql = strSql2.ToString(), SqlParameters = parameters2 });
            #endregion

            if (rechargemodel.Status == RechargeStatus.Audited)
            {
                //资金变动记录
                //更新资产
                var listHistory = new List<AssetHistoryItem>();
                listHistory.Add(new AssetHistoryItem(AssetOperateType.Plus, AssetHistoryType.Balance, rechargemodel.ToAmount));
                dict.Add(new SqlAndParams { Sql = new Member_AssetHistoryDAL().GetSqlByOperateAsset(rechargemodel.MemberAccount, statusRecordModel.Creator, rechargemodel.RechargeID.ToString(), AssetHistoryChangeType.Recharge, listHistory) });
            }

            return DbHelperSQL.ExecuteSqlTran(dict);
        }

        /// <summary>
        /// 按日期统计充值金额和数量
        /// </summary>
        /// <returns></returns>
        public DataTable GetRechargeChart()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select [Date]=convert(varchar(10), CreateTime, 111),Amount=sum(ToAmount),[Count]=count(0) 
                            from [dbo].[Member_Recharge] 
                            where [Status]=3 and convert(varchar(10), CreateTime, 111) >= convert(varchar(10), getdate()-30, 111)
                            group by convert(varchar(10), CreateTime, 111)");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            DataTable dt = ds.Tables[0];
            return dt;
        }

        /// <summary>
        /// 生成ID
        /// </summary>
        /// <returns></returns>
        public Int64 GenerateID()
        {
            var id = (Int64)DbHelperSQL.GetSingle("select isnull(max(RechargeID),0) from [dbo].[Member_Recharge]");

            return id == 0 ? 60000000 : id + 1;
        }
    }
}


